go

exec dbo.p_drop_proc_if_exists 'meta_add_role';

go

--Function of adding a role
create procedure meta_add_role(
    --Role name
    @a_role_name varchar(max))
with execute as owner
as
begin
	set nocount on;
	declare
	--Row count
    @a_row_count numeric;
    
    select @a_row_count = count(*)
    from meta_roles
    where role_name = lower(@a_role_name);
    
    if @a_row_count > 0
    begin
        raiserror('A role with the specified name was already added (role_name = %s) (#error_code = 20034)',
			16, 1, @a_role_name);
		return;
    end;
    
    set xact_abort on;
    begin tran;

	--If there is no role with that name
	if not exists (
		select * from sys.database_principals 
		WHERE name = @a_role_name and type = 'R')
		--Creating a role
		exec ('create role ' + @a_role_name);

    --Updating the metadata
    insert into meta_roles (role_name)
    values(lower(@a_role_name));
    
    commit;
end;

go

exec dbo.p_drop_proc_if_exists 'p_check_role_exists';

go

--Function of checking role existence
create procedure p_check_role_exists(
    --Role name
    @a_role_name varchar(26),
    --Result
    @a_result numeric(1) output)
as
begin
	declare
	--Row count
    @a_row_count numeric;
    
    select @a_row_count = count(*)
    from meta_roles
    where role_name = lower(@a_role_name);
    
    if @a_row_count = 0
    begin
        raiserror('A role with the specified name was not found (role_name = %s) (#error_code = 20035)',
        16, 1, @a_role_name);
        set @a_result = 0;
        return;
    end;
    
    set @a_result = 1;
end;

go

exec dbo.p_drop_proc_if_exists 'meta_delete_role';

go

--Function of deleting a role
create procedure meta_delete_role(
    --Role name
    @a_role_name varchar(max))
with execute as owner
as
begin
	set nocount on;
	declare
	--Row count
    @a_row_count numeric,
    --Result
    @a_result numeric(1);
    
    exec dbo.p_check_role_exists @a_role_name, @a_result output;
    
    if @a_result = 0 return;
    
    set xact_abort on;
    begin tran;
    
    --Getting affected users
    select user_name into #a_affected_users
    from dbo.p_users_affected_by_principal(@a_role_name);
    
    --Deleting user enrollments
    declare
    --Enrolled user name
    @a_enrolled_user_name varchar(26),
    --Enrolled user cursor
    @a_enrolled_user_cursor cursor;
    
    set @a_enrolled_user_cursor = cursor fast_forward read_only for
    select user_name from meta_user_roles
    where container_role_name = @a_role_name;
    
    open @a_enrolled_user_cursor;
    fetch next from @a_enrolled_user_cursor into @a_enrolled_user_name;
    
    while @@fetch_status = 0
    begin
		exec dbo.meta_delete_user_from_role @a_enrolled_user_name, @a_role_name;
		fetch next from @a_enrolled_user_cursor into @a_enrolled_user_name;
    end;
    
    close @a_enrolled_user_cursor;
    deallocate @a_enrolled_user_cursor;
    
    --Deleting role enrollments
    declare
    --Enrolled role name
    @a_enrolled_role_name varchar(26),
    --Enrolled role cursor
    @a_enrolled_role_cursor cursor;
    
    set @a_enrolled_role_cursor = cursor fast_forward read_only for
    select role_name from meta_role_roles
    where container_role_name = @a_role_name;
    
    open @a_enrolled_role_cursor;
    fetch next from @a_enrolled_role_cursor into @a_enrolled_role_name;
    
    while @@fetch_status = 0
    begin
		exec dbo.meta_delete_role_from_role @a_enrolled_role_name, @a_role_name;
		fetch next from @a_enrolled_role_cursor into @a_enrolled_role_name;
    end;
    
    close @a_enrolled_role_cursor;
    deallocate @a_enrolled_role_cursor;
    
    --Updating the metadata
    delete from meta_roles
    where role_name = lower(@a_role_name);
    
    --Deleting all role acl
    delete from meta_table_acl
    where principal_name = lower(@a_role_name);
    
    --Deleting a role    
    exec ('drop role ' + @a_role_name);
    
    declare
    --Affected user name
    @a_affected_user_name varchar(26),
    --Affected user cursor
    @a_affected_user_cursor cursor;
    
    --Updating role users acl
    set @a_affected_user_cursor = cursor fast_forward read_only for
    select user_name from #a_affected_users;
    
    open @a_affected_user_cursor;
    fetch next from @a_affected_user_cursor into @a_affected_user_name;
    
    while @@fetch_status = 0
    begin
		exec dbo.p_refresh_user_acl @a_affected_user_name;
		fetch next from @a_affected_user_cursor into @a_affected_user_name;
    end;
    
    close @a_affected_user_cursor;
    deallocate @a_affected_user_cursor;
    
    commit;
end;

go

exec dbo.p_drop_proc_if_exists 'meta_add_user_in_role';

go

--Function of adding user in role
create procedure meta_add_user_in_role(
    --User name
    @a_user_name varchar(max),
    --Role name
    @a_role_name varchar(max))
with execute as owner
as
begin
	set nocount on;
	declare
	--Row count
    @a_row_count numeric,
    --Result
    @a_result numeric(1);
    
    exec dbo.p_check_user_exists @a_user_name, @a_result output;
    
    if @a_result = 0 return;
    
    exec dbo.p_check_role_exists @a_role_name, @a_result output;
    
    if @a_result = 0 return;
    
    --Searching for user enrollment
    select @a_row_count = count(*)
    from meta_user_roles
    where user_name = lower(@a_user_name) 
    and container_role_name = lower(@a_role_name);
    
    if @a_row_count > 0
    begin
        raiserror('Adding a user in a role twice is forbidden (role_name = %s, user_name = %s) (#error_code = 20037)',
			16, 1, @a_role_name, @a_user_name);
        return;
    end;
    
    --Enrolling user in role
    declare @retstat int;
    exec @retstat = sp_addrolemember @a_role_name, @a_user_name;
    
    --If something went wrong
    if @retstat <> 0 return;
    
    set xact_abort on;
    begin tran;
    
    --Updating the metadata
    insert into meta_user_roles (user_name, container_role_name)
    values(lower(@a_user_name), lower(@a_role_name));
    
    --Refreshing user acl
    exec dbo.p_refresh_user_acl @a_user_name;
    
    commit;
end;

go

exec dbo.p_drop_proc_if_exists 'meta_delete_user_from_role';

go

--Function of removing user from role
create procedure meta_delete_user_from_role(
    --User name
    @a_user_name varchar(max),
    --Role name
    @a_role_name varchar(max))
with execute as owner
as
begin
	set nocount on;
	declare
	--Row count
    @a_row_count numeric,
    --Result
    @a_result numeric(1);
    
    exec dbo.p_check_user_exists @a_user_name, @a_result output;
    
    if @a_result = 0 return;
    
    exec dbo.p_check_role_exists @a_role_name, @a_result output;
    
    if @a_result = 0 return;
    
    --Searching for user enrollment
    select @a_row_count = count(*)
    from meta_user_roles
    where user_name = lower(@a_user_name)
    and container_role_name = lower(@a_role_name);
    
    if @a_row_count = 0
    begin
        raiserror('Specified user enrollment was not found (role_name = %s, user_name = %s) (#error_code = 20039)',
        16, 1, @a_role_name, @a_user_name);
        return;
    end;
    
    set xact_abort on;
    begin tran;
    
    --Deleting user from role
    declare @retstat int;
    exec sp_droprolemember @a_role_name, @a_user_name;
    
    if @retstat <> 0 return; --If something went wrong

    --Updating the metadata
    delete from meta_user_roles
    where user_name = lower(@a_user_name) 
    and container_role_name = lower(@a_role_name);
    
    --Refreshing user acl
    exec dbo.p_refresh_user_acl @a_user_name;
    
    commit;
end;

go

exec dbo.p_drop_proc_if_exists 'meta_add_role_in_role';

go

--Function of adding role in role
create procedure meta_add_role_in_role(
    --Role name
    @a_role_name varchar(max),
    --Container role name
    @a_container_role_name varchar(max))
with execute as owner
as
begin
	set nocount on;
	declare
    --Row count
    @a_row_count numeric,
    --Result
    @a_result numeric(1);
    
    exec dbo.p_check_role_exists @a_container_role_name, @a_result output;
    
    if @a_result = 0 return;
    
    exec dbo.p_check_role_exists @a_role_name, @a_result output;
    
    if @a_result = 0 return;
    
    --Searching for user enrollment
    select @a_row_count = count(*)
    from meta_role_roles
    where role_name = lower(@a_role_name) 
    and container_role_name = lower(@a_container_role_name);
    
    if @a_row_count > 0
    begin
        raiserror('Adding a role in a role twice is forbidden (container_role_name = %s, role_name = %s) (#error_code = 20038)',
			16, 1, @a_container_role_name, @a_role_name);
		return;
    end;
    
    --Adding role in role
    declare @retstat int;
    exec @retstat = sp_addrolemember @a_container_role_name, @a_role_name;
	
	if @retstat <> 0 return; --If something went wrong
	
	set xact_abort on;
    begin tran;	
    
    --Updating the metadata
    insert into meta_role_roles(role_name, container_role_name)
    values(lower(@a_role_name), lower(@a_container_role_name));
    
    declare
    --Affected user name
    @a_affected_user_name varchar(26),
    --Affected user cursor
    @a_affected_user_cursor cursor;
    
    --Updating users acl
    set @a_affected_user_cursor = cursor fast_forward read_only for
    select user_name from dbo.p_users_affected_by_principal(@a_affected_user_name);
    
    open @a_affected_user_cursor;
    fetch next from @a_affected_user_cursor into @a_affected_user_name;
    
    while @@fetch_status = 0
    begin
		exec dbo.p_refresh_user_acl @a_affected_user_name;
		fetch next from @a_affected_user_cursor into @a_affected_user_name;
    end;
    
    close @a_affected_user_cursor;
    deallocate @a_affected_user_cursor;
    
    commit;
end;

go

exec dbo.p_drop_proc_if_exists 'meta_delete_role_from_role';

go

--Function of removing role from role
create procedure meta_delete_role_from_role(
    --Role name
    @a_role_name varchar(max),
    --Container role name
    @a_container_role_name varchar(max))
with execute as owner
as
begin
	set nocount on;
	declare
	--Row count
    @a_row_count numeric,
    --Result
    @a_result numeric(1);
    
    exec dbo.p_check_role_exists @a_container_role_name, @a_result output;
    
    if @a_result = 0 return;
    
    exec dbo.p_check_role_exists @a_role_name, @a_result output;
    
    if @a_result = 0 return;
    
    --Searching for role enrollment
    select @a_row_count = count(*)
    from meta_role_roles
    where role_name = lower(@a_role_name)
    and container_role_name = lower(@a_container_role_name);
    
    if @a_row_count = 0
    begin
        raiserror('Specified role enrollment was not found (container_role_name = %s, role_name = %s) (#error_code = 20040)',
        16, 1, @a_container_role_name, @a_role_name);
        return;
    end;
    
    declare @retstat int;
    exec sp_droprolemember @a_container_role_name, @a_role_name;
    
    if @retstat <> 0 return; --If something went wrong
    
    set xact_abort on;
    begin tran;
    
    --Updating the metadata
    delete from meta_role_roles
    where  role_name = lower(@a_role_name)
    and container_role_name = lower(@a_container_role_name);
    
    declare
    --Affected user name
    @a_affected_user_name varchar(26),
    --Affected user cursor
    @a_affected_user_cursor cursor;
    
    --Updating users acl
    set @a_affected_user_cursor = cursor fast_forward read_only for
    select user_name from dbo.p_users_affected_by_principal(@a_role_name);
    
    open @a_affected_user_cursor;
    fetch next from @a_affected_user_cursor into @a_affected_user_name;
    
    while @@fetch_status = 0
    begin
		exec dbo.p_refresh_user_acl @a_affected_user_name;
		fetch next from @a_affected_user_cursor into @a_affected_user_name;
    end;
    
    close @a_affected_user_cursor;
    deallocate @a_affected_user_cursor;
	
	commit;
end;